﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using BusinessLogic;
using System.Data;
using System.Web.Security;
using System.Reflection;
using System.Data.Common;

namespace DataAccessLogic
{
    public static class DataAccess
    {
        #region Fields
        private static string _connectionStringKey = "SEP";
        #endregion

        #region Methods
        public static bool CheckForBooleanColumn(DataRow row, string columnName)
        {
            return CheckForNullColumn<bool>(row, columnName);
        }

        public static DateTime CheckForDateTimeColumn(DataRow row, string columnName)
        {
            return CheckForNullColumn<DateTime>(row, columnName);
        }
        
        public static Decimal CheckForDecimalColumn(DataRow row, string columnName)
        {
            return CheckForNullColumn<Decimal>(row, columnName);
        }

        public static Guid CheckForGuidColumn(DataRow row, string columnName)
        {
            return CheckForNullColumn<Guid>(row, columnName);
        }

        public static int CheckForIntColumn(DataRow row, string columnName)
        {
            return CheckForNullColumn<int>(row, columnName);
        }

        private static T CheckForNullColumn<T>(DataRow row, string columnName)
        {
            try
            {
                if (row.IsNull(columnName))
                {
                    return default(T);
                }
                else
                {
                    return (T)row[columnName];
                }
            }
            catch
            {
                return default(T);
            }
        }
        
        public static string CheckForStringColumn(DataRow row, string columnName)
        {
            return CheckForNullColumn<string>(row, columnName);
        }

        public static void Create(string storedProcedure, List<SqlParameter> parms)
        {
            ExecuteNonQuery(storedProcedure, parms);
        }

        public static object Create(string storedProcedure, string outputParameterName, List<SqlParameter> parms)
        {
            object result = null;

            DatabaseWrapper db = ExecuteNonQuery(storedProcedure, parms);

            if (null != db)
            {
                result = db.OutputParameterValue(outputParameterName);
            }

            return result;
        }

        public static DatabaseWrapper Delete(string storedProcedure, Guid id)
        {
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@ID", id));
            return Delete(storedProcedure, parms);
        }

        public static DatabaseWrapper Delete(string storedProcedure, List<SqlParameter> parms)
        {
            return ExecuteNonQuery(storedProcedure, parms);
        }

        private static DatabaseWrapper ExecuteNonQuery(string storedProcedure, List<SqlParameter> parms)
        {
            DatabaseWrapper db = null;

            if (!string.IsNullOrWhiteSpace(storedProcedure))
            {
                db = new DatabaseWrapper(_connectionStringKey, storedProcedure, parms);
                
                try
                {
                    db.ExecuteNonQuery();
                }
                catch (Exception dbex)
                {
                    db.Exceptions.Add(dbex);
                    throw dbex;
                }                
            }

            return db;
        }        
        
        public static DataTable Get(string storedProcedure)
        {
            return Get(storedProcedure, null);
        }

        public static DataTable Get(string storedProcedure, List<SqlParameter> parms)
        {
            DataTable dt = null;

            if (!string.IsNullOrWhiteSpace(storedProcedure))
            {
                DatabaseWrapper db = new DatabaseWrapper(_connectionStringKey, storedProcedure, parms);
                dt = db.ExecuteDataTable();
            }

            return dt;
        }
        
        public static void Update(string storedProcedure, List<SqlParameter> parms)
        {
            ExecuteNonQuery(storedProcedure, parms);
        }
        #endregion                            
    }
}